package com.ltmall.api.dao.impl;

import com.ltmall.api.bean.ActivityPrice;
import com.ltmall.api.bean.Cart;
import com.ltmall.api.bean.ThSku;
import com.ltmall.api.dao.ICartDAO;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.springframework.orm.hibernate5.HibernateCallback;

import java.math.BigInteger;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import static com.ltmall.api.util.HibernateUtil.getSession;

public class CartDAO extends BaseEntityDAO<Cart> implements ICartDAO {

    public boolean setNumber(int number, int cartID) {
        // TODO Auto-generated method stub
        String hql = "update Cart cart set cart.number = ?" + "where cart.cartID = ?";
        try {
            Session session = getSession();
            Query query = session.createQuery(hql);
            query.setParameter(0, number);
            query.setParameter(1, cartID);
            int temp = query.executeUpdate();
            if (temp == 1) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    public boolean delCart(int cartID) {
        // TODO Auto-generated method stub
        String hql = "delete Cart cart " + "where cart.cartID = ?";
        try {
            Session session = getSession();
            Query query = session.createQuery(hql);
            query.setParameter(0, cartID);
            int temp = query.executeUpdate();
            if (temp == 1) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    @SuppressWarnings("rawtypes")
    public List<?> cartList(final int userID, final int shopID) {
        // TODO Auto-generated method stub
        final String SQL = "SELECT CART.CART_ID,GOODS.GOODS_ID,GOODS.GOODS_TITLE,GOODS.GOODS_PICTURE,GOODS.GOODS_TYPE,CART.AMOUNT,CART.GOODS_FORMAT,CART.ACTIVITY_ID,CART.ACTIVITY_NAME, CART.SKU_ID"
                           + " from FLAVOR_GOODS GOODS left join FLAVOR_CART CART on GOODS.GOODS_ID = CART.GOODS_ID "
                           + "where CART.USER_ID = ? and CART.SHOP_ID = ?";
        List<?> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createSQLQuery(SQL);
                query.setParameter(0, userID);
                query.setParameter(1, shopID);
                return query.list();
            }
        });
        return list;
    }

    @SuppressWarnings("rawtypes")
    public List<?> shopList(final int userID) {
        // TODO Auto-generated method stub
        final String SQL = "SELECT DISTINCT SHOP.SHOP_ID as shopID,SHOP.SHOP_NAME as shopName"
                           + " from FLAVOR_SHOP SHOP left join FLAVOR_CART CART on SHOP.SHOP_ID = CART.SHOP_ID "
                           + "where CART.USER_ID = ?";
        List<?> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createSQLQuery(SQL);
                //						query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                query.setParameter(0, userID);
                return query.list();
            }
        });
        return list;
    }

    @SuppressWarnings("rawtypes")
    public List<?> shopAndgoodsList(final int userID, final int directID) {

        List<Integer> directs = new ArrayList<Integer>();
        if (directID != 0) {
            //directs.add(0);
            directs.add(directID);
        } else {
            directs.add(0);
        }

        final List<Integer> lists = directs;

        // TODO Auto-generated method stub
        final String SQL = "SELECT DISTINCT SHOP.SHOP_ID ,SHOP.SHOP_NAME ,CART.CART_ID,GOODS.GOODS_ID,"
                           + "GOODS.GOODS_TITLE,GOODS.GOODS_PICTURE,GOODS.GOODS_TYPE,CART.AMOUNT,sku.GOODS_FORMAT,CART.ACTIVITY_ID,CART.ACTIVITY_NAME, sku.GOODS_PRICE, cart.PRICE, sku.GOODS_STOCK, sku.HIDDEN, GOODS.SHOW, GOODS.STATION, sku.GOODS_PROFIT_SHARE,cart.DIRECT_ID,GOODS.GOODS_NAME "
                           + "FROM FLAVOR_SHOP SHOP INNER JOIN FLAVOR_CART CART ON SHOP.SHOP_ID = CART.SHOP_ID INNER JOIN FLAVOR_GOODS GOODS ON CART.GOODS_ID = GOODS.GOODS_ID INNER JOIN th_sku sku ON CART.SKU_ID = sku.ID "
                           + "WHERE CART.USER_ID=? AND  CART.DIRECT_ID IN (:lists) "
                           + "ORDER BY SHOP_ID";
        List<?> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createSQLQuery(SQL);
                //						query.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
                query.setParameter(0, userID);
                query.setParameterList("lists", lists);
                return query.list();
            }
        });
        return list;
    }

    @SuppressWarnings("rawtypes")
    public int cartListCount(final int userID) {
        // TODO Auto-generated method stub
        final String SQL = "SELECT COUNT(*) "
                           + "FROM FLAVOR_SHOP SHOP INNER JOIN FLAVOR_CART CART ON SHOP.SHOP_ID = CART.SHOP_ID INNER JOIN FLAVOR_GOODS GOODS ON CART.GOODS_ID = GOODS.GOODS_ID "
                           + "WHERE CART.USER_ID=? ";
        Session session = this.getSession();
        SQLQuery query = session.createSQLQuery(SQL);
        query.setParameter(0, userID);
        int c = ((BigInteger) query.uniqueResult()).intValue();
        return c;
    }

    @SuppressWarnings("rawtypes")
    public Cart cartDetail(int cartID) {
        List list = this.getHibernateTemplate().find("from Cart car where car.cartID =?",
            new Object[] { cartID });
        if (list.size() > 0) {
            return (Cart) list.get(0);
        }
        return null;
    }

    @SuppressWarnings("rawtypes")
    public Cart isGoodsExist(int goodsID, int userID, String format) {
        List list = this
            .getHibernateTemplate()
            .find(
                "from Cart car where car.goodsID =? and car.userID = ? and car.goodsFormat = ? and car.activityID = 0",
                //"from Cart car where car.goodsID =? and car.userID = ? and car.goodsFormat = ? ",
                new Object[] { goodsID, userID, format });
        if (list.size() > 0) {
            return (Cart) list.get(0);
        }
        return null;
    }

    @SuppressWarnings("rawtypes")
    public Cart isGroupGoodsExist(int goodsID, int userID, String format, int directID) {
        List list = this
            .getHibernateTemplate()
            .find(
                "from Cart car where car.goodsID =? and car.userID = ? and car.goodsFormat = ? and car.directID = ?",
                //"from Cart car where car.goodsID =? and car.userID = ? and car.goodsFormat = ? ",
                new Object[] { goodsID, userID, format, directID });
        if (list.size() > 0) {
            return (Cart) list.get(0);
        }
        return null;
    }

    //判断活动商品是否已经加入购物车
    @SuppressWarnings("rawtypes")
    public Cart isGoodsExist(int goodsID, int userID, int skuID, int activityID, String activityName) {
        List list = this.getHibernateTemplate().find(
            "from Cart car where car.goodsID =? and car.userID = ? and car.skuID = ? and car.activityID = ? "
                    + "and car.activityName = ?",
            new Object[] { goodsID, userID, skuID, activityID, activityName });
        if (list.size() > 0) {
            return (Cart) list.get(0);
        }
        return null;
    }

    public boolean numberAndOne(int goodsID, int userID) {
        // TODO Auto-generated method stub
        String hql = "update Cart car set car.number = car.number +1"
                     + "where car.goodsID = ? and car.userID = ?";
        try {
            Session session = getSession();
            Query query = session.createQuery(hql);
            query.setParameter(0, goodsID);
            query.setParameter(1, userID);
            int number = query.executeUpdate();
            if (number == 1) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;

    }

    public boolean delCart(int goodsID, String goodsFormat, int userID) {
        // TODO Auto-generated method stub
        String hql = "delete Cart cart where cart.goodsID = ? and cart.goodsFormat = ? and cart.userID = ?";
        try {
            Session session = getSession();
            Query query = session.createQuery(hql);
            query.setParameter(0, goodsID);
            query.setParameter(1, goodsFormat);
            query.setParameter(2, userID);
            int temp = query.executeUpdate();
            if (temp == 1) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    public boolean delCartBySkuID(int skuID, int userID) {
        // TODO Auto-generated method stub
        String hql = "delete Cart cart where cart.skuID = ? and cart.userID = ?";
        try {
            Session session = getSession();
            Query query = session.createQuery(hql);
            query.setParameter(0, skuID);
            query.setParameter(1, userID);
            int temp = query.executeUpdate();
            if (temp == 1) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    public boolean updateCart(int cartID, int number, String goodsFlavor) {
        // TODO Auto-generated method stub
        String hql = "update Cart cart set cart.number = ?,cart.goodsFlavor = ?"
                     + "where cart.cartID = ?";
        try {
            Session session = getSession();
            Query query = session.createQuery(hql);
            query.setParameter(0, number);
            query.setParameter(1, goodsFlavor);
            query.setParameter(2, cartID);
            int temp = query.executeUpdate();
            if (temp == 1) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    @SuppressWarnings("rawtypes")
    public ThSku getPrice(int goodsID, String format) {
        List list = this.getHibernateTemplate().find(
            "from ThSku p where p.goodsID =? and p.format = ?", new Object[] { goodsID, format });
        if (list.size() > 0) {
            return (ThSku) list.get(0);
        }
        return null;
    }

    @SuppressWarnings("rawtypes")
    public ActivityPrice getActivityPrice(int goodsID, String goodsFormat) {
        long cTime = System.currentTimeMillis();
        String hql;
        Object[] obj;
        if (goodsFormat != null) {
            hql = "from ActivityPrice ap where ap.goodsID =? and ap.format = ? and ap.startTime<=? and ap.endTime>?";
            obj = new Object[] { goodsID, goodsFormat, cTime, cTime };
        } else {
            hql = "from ActivityPrice ap where ap.goodsID =? and ap.startTime<=? and ap.endTime>?";
            obj = new Object[] { goodsID, cTime, cTime };
        }
        List list = this.getHibernateTemplate().find(hql, obj);
        if (list.size() > 0) {
            return (ActivityPrice) list.get(0);
        }
        return null;
    }

    @SuppressWarnings("rawtypes")
    public ActivityPrice panicSKuGet(int skuID, int acitivityID) {
        long cTime = System.currentTimeMillis();
        String hql;
        Object[] obj;
        hql = "from ActivityPrice ap where ap.skuID =? and ap.startTime<=? and ap.endTime>?";
        obj = new Object[] { skuID, cTime, cTime };
        List list = this.getHibernateTemplate().find(hql, obj);
        if (list.size() > 0) {
            return (ActivityPrice) list.get(0);
        }
        return null;
    }

    //获取购买的活动订单
    @SuppressWarnings("rawtypes")
    public List<?> ordersGet(final int skuID, final int userID, final String activityName,
                             final int getActivityID) {
        final String SQL = "SELECT OCONTENT.AMOUNT "
                           + " from FLAVOR_ORDER_CONTENT OCONTENT left join FLAVOR_ORDER FORDER on OCONTENT.ORDER_ID = FORDER.ORDER_ID "
                           + " where OCONTENT.SKU_ID = ? and FORDER.USER_ID = ? and OCONTENT.ACTIVITY_NAME = ? and OCONTENT.ACTIVITY_ID = ? and FORDER.STATION >= 2";
        List<?> list = this.getHibernateTemplate().execute(new HibernateCallback<List>() {
            public List doInHibernate(Session session) throws HibernateException, SQLException {
                Query query = session.createSQLQuery(SQL);
                query.setParameter(0, skuID);
                query.setParameter(1, userID);
                query.setParameter(2, activityName);
                query.setParameter(3, getActivityID);
                return query.list();
            }
        });
        return list;
    }

    //获取规格
    public ThSku skuGet(int skuId) {
        List<?> list = this.getHibernateTemplate().find("from ThSku skus where skus.ID =?",
            new Object[] { skuId });
        if (list.size() > 0) {
            return (ThSku) list.get(0);
        }
        return null;
    }

}
